1.37 MySQL Flush

1. What Is Flush - When the contents of an in-memory data page and an on-disk data page are inconsistent, the memory page is called a dirty page. After the memory data is written to disk, the contents of the memory and disk data pages become consistent, and it is called a clean page - Flush means flushing dirty pages to disk 2. When Flush Is Triggered - redo-log is full - memory is full - when MySQL considers the system idle - when MySQL shuts down normally 3. InnoDB Dirty-Page Flushing Control Strategy

DatabasesCreated 07/18/2021Updated 07/18/20211 min readhistorical

This is a historical learning note and may contain outdated or incomplete understanding.

1. What Is Flush

When the contents of an in-memory data page and an on-disk data page are inconsistent, we call the memory page a “dirty page.” After the memory data is written to disk, the contents of the memory and disk data pages become consistent, and it is called a “clean page.”
Flush means flushing dirty pages to disk.

2. When Flush Is Triggered

Dirty pages are flushed in several situations:
- the redo-log is full (try to avoid this, because it blocks all updates);
- memory is full;
- when MySQL considers the system idle (little impact);
- when MySQL shuts down normally (little impact).

Impact of Flush

MySQL performance may fluctuate briefly.

3. InnoDB Dirty-Page Flushing Control Strategy

First use fio to measure disk IOPS, then set innodb_io_capacity to the corresponding value. For example, the original note used the following command for measurement:

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest